package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.biff.FontRecord;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.net.ntp.TimeStamp;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.jboss.util.propertyeditor.BigDecimalEditor;
import org.joda.time.DateTime;

import com.lowagie.text.Font;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;
import org.utils.DataUtils;

public class MM_TakingInventory extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	BigDecimal ad_org_id = null;
	BigDecimal m_warehouse_id = null;
	BigDecimal m_locator_id = null;
	BigDecimal chiefComm = null;
	BigDecimal memberComm1 = null;
	BigDecimal memberComm2 = null;
	BigDecimal memberComm3 = null;
	BigDecimal memberComm4 = null;
	
	String getSQlresult = null;

	private static CLogger s_log = CLogger.getCLogger(BalanceSheets.class);
	/** */
	private String path = "";

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("ad_org_id")) 
				ad_org_id  = (BigDecimal) para[i].getParameter();
			else if (name.equals("m_warehouse_id")) 
				m_warehouse_id  = (BigDecimal) para[i].getParameter();
			else if (name.equals("m_locator_id")) 
				m_locator_id  = (BigDecimal) para[i].getParameter();
			else if (name.equals("chiefComm")) 
				chiefComm  = (BigDecimal) para[i].getParameter();
			else if (name.equals("memberComm1")) 
				memberComm1  = (BigDecimal) para[i].getParameter();
			else if (name.equals("memberComm2")) 
				memberComm2  = (BigDecimal) para[i].getParameter();
			else if (name.equals("memberComm3")) 
				memberComm3  = (BigDecimal) para[i].getParameter();
			else if (name.equals("memberComm4")) 
				memberComm4  = (BigDecimal) para[i].getParameter();			
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		path = Util.localFilePath;
		// / here another code)
	}

	@Override
	protected String doIt() throws Exception {
		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "MM_TakingInventory_tmpl.xls";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate") + " " + templateName);

		File templateCopy = new File(Util.localFilePath + "MM_TakingInventory.xls");
		File temporaryFile = entry.getFile(Util.localFilePath + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}

		
		//
//		// ExcelCell cellStart = Utils.getCellStart(tableBook,">>");
//		// Style for value column
		WritableCellFormat valuestyle = new WritableCellFormat();
		valuestyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setAlignment(Alignment.RIGHT);
		valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		valuestyle.setWrap(true);

		WritableCellFormat vsleft = new WritableCellFormat();
		vsleft.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsleft.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsleft.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsleft.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsleft.setAlignment(Alignment.LEFT);
		vsleft.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsleft.setWrap(true);		

		WritableCellFormat vsCenter = new WritableCellFormat();
		vsCenter.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsCenter.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsCenter.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsCenter.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsCenter.setAlignment(Alignment.CENTRE);
		vsCenter.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsCenter.setWrap(true);		
		
		
		
		WritableFont arial10ptNoBold = new WritableFont(WritableFont.TIMES, 11,	WritableFont.NO_BOLD);
		WritableCellFormat	vsNumber = new WritableCellFormat(arial10ptNoBold,NumberFormats.THOUSANDS_FLOAT);		

		vsNumber.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsNumber.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsNumber.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsNumber.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsNumber.setAlignment(Alignment.RIGHT);
		vsNumber.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsNumber.setWrap(true);		
		
		List<line> l = getDatas();
		if (getSQlresult!=null)
			return Msg.translate(m_ctx, "Ошибка запроса.");

		int count = l.size(); 
		if( count == 0)
			return Msg.translate(m_ctx, "По заданому параметру нет данных");
//		
//		// caption -------------------------------------
//		String caption = " АКТ № " + DocumentNo + " от " +  new SimpleDateFormat("dd.MM.yyyy").format(l.get(0).movementdate);
//		
//		WritableFont arial10ptBold = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
//		WritableCellFormat captionStyle = new WritableCellFormat(arial10ptBold);
//		captionStyle.setAlignment(Alignment.CENTRE);
//		captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
//		
//		label = new Label(2, 8, caption, captionStyle);
//		sheet.addCell(label);
//		
//		caption  = "На списание ТМЗ, израсходованных в " 
//					+ DataUtils.getMonthNameRUE(l.get(0).movementdate) 
//					+" месяце "
//					+Integer.valueOf(l.get(0).movementdate.getYear() + 1900).toString() 
//					+ " года";
//		
//		WritableFont arial9ptBold = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD);
//		captionStyle = new WritableCellFormat(arial9ptBold);		
//		
//		label = new Label(2, 9, caption, captionStyle);
//		sheet.addCell(label);
		
		//-----------------------------------------------		
		int row = 44;

		
		for(int i=0; i < count; i++, row++){
			if(i > 0)
				sheet.insertRow(row);
			line li = l.get(i);
//		// output datas ----------------------------------{
			label = new Label(0, row, Integer.valueOf(row - 43).toString(), valuestyle);
			sheet.addCell(label);			
			
//			String value = li.v2;
//
//			int len = value.length();
//			int height = 0; 
//			if(len > 0){
//			 int diff = len / 49;
//			 if(diff > 0){
//				 height *= 300;
//				 label.setHeight((short)height);
//				 sheet.setRowHeight();
//				 
//			 }
//			}

			
			
			label = new Label(1, row, li.v2, vsleft);
			sheet.mergeCells(1, row, 4, row);
			sheet.addCell(label);
			label = new Label(5, row, li.v3, valuestyle);
			sheet.mergeCells(5, row, 6, row);
			sheet.addCell(label);
			label = new Label(7, row, li.v4, vsCenter);
			sheet.mergeCells(7, row, 8, row);
			sheet.addCell(label);			
			label = new Label(9, row, li.v5, vsCenter);
			sheet.mergeCells(9, row, 10, row);
			sheet.addCell(label);			
			BigDecimal b = li.v6.setScale(2, BigDecimal.ROUND_HALF_UP);
			label = new Label(11, row, b.toString().replace('.', ','), valuestyle);
			sheet.mergeCells(11, row, 12, row);
			sheet.addCell(label);

//			label = new Label(13, row, (new BigDecimal(0)).toString().replace('.', ','), valuestyle);
			number = new Number(13, row, new BigDecimal(0).doubleValue(), vsNumber);
			sheet.addCell(number);

			number = new Number(14, row, new BigDecimal(0).doubleValue(), vsNumber);
			sheet.addCell(number);

			
			BigDecimal v16 = li.v9;
			number = new Number(15, row, v16.doubleValue(), vsNumber);
			sheet.addCell(number);
			
			BigDecimal v17 = v16.multiply(li.v6);
			b = v17.setScale(2,BigDecimal.ROUND_HALF_UP);
			number = new Number(16, row, b.doubleValue(), vsNumber);
			sheet.addCell(number);
		}
		// output membersb
		WritableCellFormat vsMember = new WritableCellFormat();
		vsMember.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsMember.setAlignment(Alignment.CENTRE);
		vsMember.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsMember.setWrap(true);
		
		member m = null;
		row += 18;
		if(chiefComm!=null){
			m = getMember(chiefComm);
			label = new Label(3, row, m.position, vsMember);
			sheet.mergeCells(3, row, 7, row);
			sheet.addCell(label);
			label = new Label(14, row, m.fio, vsMember);
			sheet.mergeCells(14, row, 16, row);
			sheet.addCell(label);
		}
		row += 2;
		if(memberComm1!=null){
			m = getMember(memberComm1);
			label = new Label(3, row, m.position, vsMember);
			sheet.mergeCells(3, row, 7, row);
			sheet.addCell(label);
			label = new Label(14, row, m.fio, vsMember);
			sheet.mergeCells(14, row, 16, row);
			sheet.addCell(label);	
		}
		row += 2;
		if(memberComm2!=null){
			m = getMember(memberComm2);
			label = new Label(3, row, m.position, vsMember);
			sheet.mergeCells(3, row, 7, row);
			sheet.addCell(label);
			label = new Label(14, row, m.fio, vsMember);
			sheet.mergeCells(14, row, 16, row);
			sheet.addCell(label);
		}
		row += 2;
		if(memberComm3!=null){
			m = getMember(memberComm3);
			label = new Label(3, row, m.position, vsMember);
			sheet.mergeCells(3, row, 7, row);
			sheet.addCell(label);
			label = new Label(14, row, m.fio, vsMember);
			sheet.mergeCells(14, row, 16, row);
			sheet.addCell(label);
		}
		row += 2;
		if(memberComm4!=null){
			m = getMember(memberComm4);
			label = new Label(3, row, m.position, vsMember);
			sheet.mergeCells(3, row, 7, row);
			sheet.addCell(label);
			label = new Label(14, row, m.fio, vsMember);
			sheet.mergeCells(14, row, 16, row);
			sheet.addCell(label);
		}		
		//output datas -----------------------------------------}
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();

		try {
			String fileName = Util.localFilePath + "MM_TakingInventory.xls";
			String command = "excel \"" + fileName + "\"";
			Runtime.getRuntime().exec("cmd /c start " + command);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return Msg.translate(m_ctx, "Success");
	}



	private List<line> getDatas() {
		List<line> list = new ArrayList<line>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		StringBuilder sql = getSQl();

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				line l = new line();
				l.v2 = rs.getString("v2");
				l.v3 = rs.getString("v3");
				l.v4 = rs.getString("v4");
				l.v5 = rs.getString("v5");
				l.v6 = rs.getBigDecimal("v6");
				l.v9 = rs.getBigDecimal("v9");
				
				list.add(l);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: MM_TakingInventory", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return list;
	}

	private member getMember(BigDecimal memberID) {
		member m = new member();
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		StringBuilder sql = new StringBuilder(
				"	select " 
				+" 	bp.name as fio, " 
				+"	hj.name as position	" 

				+"	from c_bpartner bp	"
				+"	left join hr_employee he on he.c_bpartner_id = bp.c_bpartner_id	"
				+"	left join hr_job hj on hj.hr_job_id = he.hr_job_id	"
				+"	where bp.isemployee = 'Y'	"
				+"	and bp.c_bpartner_id = " + memberID.toString() 
				//+"	limit 1 "
				);

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);

			rs = pstmt.executeQuery();
			while (rs.next()) {		    			 
		    	String s = rs.getString("fio");
				String fio = "";
		    	String []t = s.split(" ");
		    	if(t.length > 0){
		    	 for(int i=0; i < t.length;i++)
		    		 if(i == 0)
		    			 fio += t[i] + ' ';
		    		 else{
		    			if(t[i].length() > 0)
		    			 fio += t[i].substring(0,1) + ". ";
		    		 }
		    	} 
		    	else
		    		fio = s;
		    	 
				m.fio = fio;
				m.position = rs.getString("position");
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: MM_TakingInventory", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return m;
	}
	
	class line {
		String v2 = null;
		String v3 = null;
		String v4 = null;
		String v5 = null;
		BigDecimal v6 = null;
		BigDecimal v9 = null;
	}
	
	class member{
		String fio = null;
		String position = null;
	}

	private StringBuilder getSQl() {
		StringBuilder sql = new StringBuilder();
		sql.append(   
		  " SELECT  "
		+ " p.Name as v2 "
		+ " ,(select combination  "
		+ " 				from c_validcombination as v "
								+ " left join m_product_acct as a on a.p_asset_acct = v.c_validcombination_id "
								+ " where a.m_product_id = p.m_product_id "
								+ " ) as v3 "
				+ " ,p.Value as v4 "
				+ " ,(select name from c_uom_trl where c_uom_id = p.c_uom_id) as v5 "
				+ " ,bomPriceList(p.M_Product_ID, pr.M_PriceList_Version_ID) AS v6 "

				+ " ,case when p.IsBOM='N' and (p.ProductType!='I' OR p.IsStocked='N') " 
+ " 					then to_number(get_Sysconfig('QTY_TO_SHOW_FOR_SERVICES', '99999', p.ad_client_id, 0), '99999999999') " 
					+ " else bomQtyAvailable(p.M_Product_ID,1000000,0) end v9 "
				
		+ " FROM M_Product p  "
		+ " LEFT OUTER JOIN M_ProductPrice pr ON (p.M_Product_ID=pr.M_Product_ID AND pr.IsActive='Y') " 

		+ " inner join m_storage as store on (store.m_product_id = p.m_product_id) "
		+ " inner join ad_org as org ON (org.ad_org_id = store.ad_org_id) "
		+ " inner JOIN m_locator ml ON (store.m_locator_id = ml.m_locator_id) "
		+ " inner JOIN m_warehouse mw ON (mw.ad_org_id = org.ad_org_id) "
		
		+ " WHERE p.IsActive='Y' AND p.IsSummary='N' AND (p.IsStocked='Y' AND p.IsSummary='N') "
		+ " and org.ad_org_id = '" + ad_org_id + "' and mw.m_warehouse_id = '" + m_warehouse_id + "' "
		+ " and ml.m_locator_id = '" + m_locator_id + "' "
		+ " and (case when p.IsBOM='N' and (p.ProductType!='I' OR p.IsStocked='N') " 
		+ " then to_number(get_Sysconfig('QTY_TO_SHOW_FOR_SERVICES', '99999', p.ad_client_id, 0), '99999999999')	" 
		+ " else bomQtyAvailable(p.M_Product_ID,1000000,0) end) > 0	"		
		+ " order by p.Name "

		);

		return sql;
	}
}